﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using ClassModel;
using ClassCommon;
using System.Data.SqlClient;
using System.Data;

namespace ClassDAL
{
    public class t_class_attachment_info_DAL
    {
        /// <summary>
        /// 插入用户附加信息
        /// </summary>
        /// <param name="attachment">附加信息</param>
        /// <returns>返回附加信息自增id</returns>
        public static int InsertAttachment(t_class_attachment_info attachment)
        {
            StringBuilder insertBuilder = new StringBuilder();

            insertBuilder.Append("INSERT INTO t_class_attachment_info (userId,schoolName,address,duty,ageRange,phoneNum,QQ,Email,createTime,schoolType) ");
            insertBuilder.Append(" VALUES(");
            insertBuilder.Append("@userId,@schoolName,@address,@duty,@ageRange,@phoneNum,@QQ,@Email,@createTime,@schoolType");
            insertBuilder.Append(");");
            insertBuilder.Append("SELECT @@IDENTITY;");

            SqlParameter[] insertParams = new SqlParameter[] 
            {
                new SqlParameter("@userId",attachment.userID),
                new SqlParameter("@schoolName",attachment.SchoolName),
                new SqlParameter("@address",attachment.Address),
                new SqlParameter("@duty",attachment.Duty),
                new SqlParameter("@ageRange",attachment.AgeRange),
                new SqlParameter("@phoneNum",attachment.PhoneNum),
                new SqlParameter("@QQ",attachment.QQ),
                new SqlParameter("@Email",attachment.Email),
                new SqlParameter("@createTime",attachment.createTime),
                new SqlParameter("@schoolType",attachment.SchoolType)
            };


            return Convert.ToInt32(SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, insertBuilder.ToString(), insertParams));

        }


        /// <summary>
        /// 检查当前用户附加信息是否存在
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public static int CheckAttachmentExists(int userID)
        {
            string sql = "SELECT id from t_class_attachment_info where userId = @userId;";
            SqlParameter userIDP = new SqlParameter("@userId", userID);

            var obj = SQLHelper.ExecuteScalar(SQLHelper.GetConnection(), System.Data.CommandType.Text, sql, userIDP);
            return obj == null ? 0 : Convert.ToInt32(obj);
        }


        /// <summary>
        /// 获取用户提交的附加信息列表
        /// </summary>
        /// <param name="userID">用户id</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="pageCount">每页条数</param>
        /// <returns>返回集合</returns>
        public static IList<t_class_attachment_info> List(int userID, int pageIndex = 0, int pageCount = 10)
        {
            StringBuilder listBuilder = new StringBuilder();
            listBuilder.AppendLine("select * from ");
            listBuilder.AppendLine("(select id,userid,schoolName,duty,agerange,phonenum,QQ,email ,createtime ,schoolType ,ROW_NUMBER() over (order by createtime desc) as rn from  t_class_attachment_info where userid = @userid) tmp");
            listBuilder.AppendLine("tmp where rn between @begin and @end;");

            // 1 - 10
            // 0 * 10 + 1 = 1
            // 0 * 10 + 10
            int begin = pageIndex * pageCount + 1;
            int end = pageIndex * pageCount + pageCount;

            SqlParameter[] pageParams = new SqlParameter[] 
            {
               new SqlParameter("@begin",begin),
               new SqlParameter("@end",end),
               new SqlParameter("@userid",userID)
            };


            DataSet ds = SQLHelper.ExecuteDataset(SQLHelper.GetConnection(), CommandType.Text, listBuilder.ToString(), pageParams);
            if (ds.Tables == null || ds.Tables.Count == 0) return null;

            DataTable dt = ds.Tables[0];

            List<t_class_attachment_info> list = new List<t_class_attachment_info>();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                // id,userid,schoolName,duty,agerange,phonenum,QQ,email ,createtime ,schoolType
                t_class_attachment_info model = new t_class_attachment_info();
                DataRow row = dt.Rows[i];
                model.Id = Convert.ToInt32(row["id"]);
                model.userID = Convert.ToInt32(row["userid"]);
                model.SchoolName = row["schoolName"].ToString();
                model.Duty = row["duty"].ToString();
                model.AgeRange = row["agerange"].ToString();
                model.PhoneNum = row["phoneNum"] == DBNull.Value ? string.Empty : row["phoneNum"].ToString();
                model.QQ = row["QQ"] == DBNull.Value ? string.Empty : row["QQ"].ToString();
                model.Email = row["email"] == DBNull.Value ? string.Empty : row["email"].ToString();
                model.SchoolType = row["schoolType"].ToString();

                list.Add(model);
            }

            return list;
        }


        /// <summary>
        /// 获取用户附加信息
        /// </summary>
        /// <param name="userid"></param>
        /// <returns></returns>
        public static t_class_attachment_info GetAttachmentByUserId(int userid)
        {
            string selectSQL = @"  select tcai.*,tcst.name as schoolTypeName,tcjt.name as jobTypeName,tcar.name as ageRangeName from
                                    (SELECT 
	                                id,userid,schoolName,[address],duty,ageRange,phoneNum,qq,email,createtime,schoolType 
	                                from t_class_attachment_info where userid = @userid) as tcai 
	                                left join t_class_school_Type as tcst on tcai.schoolType = tcst.id 
	                                left join t_class_job_type as tcjt on tcai.duty = tcjt.id
	                                left join t_class_ageRange as tcar on tcai.ageRange = tcar.id ";

            SqlParameter userIdP = new SqlParameter("@userid", userid);

            SqlDataReader reader = SQLHelper.ExecuteReader(SQLHelper.GetConnection(), CommandType.Text, selectSQL, userIdP);
            if (!reader.Read()) return null;

            t_class_attachment_info info = new t_class_attachment_info();

            info.Id = Convert.ToInt32(reader["id"]);
            info.userID = userid;
            info.SchoolName = reader["schoolName"].ToString();
            info.Address = reader["address"] == DBNull.Value ? string.Empty : reader["address"].ToString();
            info.Duty = reader["duty"].ToString();
            info.AgeRange = reader["ageRange"] == DBNull.Value ? string.Empty : reader["ageRange"].ToString();
            info.PhoneNum = reader["phoneNum"] == DBNull.Value ? string.Empty : reader["phoneNum"].ToString();
            info.QQ = reader["QQ"] == DBNull.Value ? string.Empty : reader["QQ"].ToString();
            info.Email = reader["email"] == DBNull.Value ? string.Empty : reader["email"].ToString();
            info.createTime = Convert.ToDateTime(reader["createtime"]);
            info.SchoolType = reader["schoolType"] == DBNull.Value ? string.Empty : reader["schoolType"].ToString();
            info.schoolTypeName = reader["schoolTypeName"].ToString();
            info.JobTypeName = reader["jobTypeName"].ToString();
            info.ageRangeName = reader["ageRangeName"].ToString();

            reader.Close();

            return info;
        }


        /// <summary>
        /// 编辑用户附加信息
        /// </summary>
        /// <param name="attachinfo"></param>
        /// <returns></returns>
        public static int UpdateAttachment(t_class_attachment_info attachinfo)
        {

            string updateSQL = @"
                                    update t_class_attachment_info set 
						            schoolName = @schoolName,
						            [address] = @address,
						            duty = @duty,
						            ageRange = @ageRange,
                                    phoneNum = @phoneNum,
						            QQ = @QQ,
						            email = @email,
						            schoolType = @schoolType
                                    where userId = @userid;";

            SqlParameter[] updateParams = new SqlParameter[] 
            {
                new SqlParameter("@schoolName",attachinfo.SchoolName),
                new SqlParameter("@address",attachinfo.Address),
                new SqlParameter("@duty",attachinfo.Duty),
                new SqlParameter("@ageRange",attachinfo.AgeRange),
                new SqlParameter("@phoneNum",attachinfo.PhoneNum),
                new SqlParameter("@QQ",attachinfo.QQ),
                new SqlParameter("@email",attachinfo.Email),
                new SqlParameter("@schoolType",attachinfo.SchoolType),
                new SqlParameter("@userid",attachinfo.userID)
            };

            return Convert.ToInt32(SQLHelper.ExecuteNonQuery(SQLHelper.GetConnection(), CommandType.Text, updateSQL, updateParams));
        }

    }
}
